package com.jdbc;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class test4 {
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String USER = "root";
    private static final String PWD = "root";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool?useUnicode=true&charset=utf8&serverTimezone=Asia/Shanghai";

    public static void main(String[] args) {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            Connection connection = DriverManager.getConnection(URL, USER, PWD);

            StringBuilder builder=new StringBuilder()
                    .append(" SELECT ")
                    .append("   s.studentNo,")
                    .append(" 	s.loginpwd,")
                    .append(" 	s.studentname,")
                    .append(" 	s.sex,")
                    .append(" 	s.gradelid,")
                    .append(" 	s.phone,")
                    .append(" 	s.address,")
                    .append(" 	s.borndate")
                    .append(" FROM students s")
                    .append(" WHERE s.bornDate > ?");

            PreparedStatement preparedStatement = connection.prepareStatement(builder.toString());
            String date_str="1999-01-01";
            SimpleDateFormat sdf=new SimpleDateFormat("YYYY-MM-DD");
            Date parse = sdf.parse(date_str);
            java.sql.Date date=new java.sql.Date(parse.getTime());
            preparedStatement.setDate(1,date);

            ResultSet resultSet = preparedStatement.executeQuery();//执行sql语句

            //用于保存学生对象
            List<Student> list=new ArrayList<>();
            while (resultSet.next()){//判断结果集中是否有数据,每有一行数据，就循环一次
                int stu_id = resultSet.getInt("studentNo");
                String loginPwd = resultSet.getString("loginpwd");
                String studentName = resultSet.getString("studentname");
                String gender = resultSet.getString("sex");
                int gradeID = resultSet.getInt("gradelid");
                String phone = resultSet.getString("phone");
                String address = resultSet.getString("address");
                java.sql.Date bornDate = resultSet.getDate("borndate");
                Date born = new Date(bornDate.getTime());//将java.sql.Date转换为java.util.Date

                //将取出的一行数据转换为java对象,也叫做pojo
                Student student=new Student();
                student.setStuId(stu_id);
                student.setLoginPwd(loginPwd);
                student.setStudentName(studentName);
                student.setGender(gender);
                student.setPhone(phone);
                student.setAddress(address);
                student.setBorn(born);

                list.add(student);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();

            list.forEach(System.out::println);


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    /**
     * Student类对应数据库中Student表
     * 一行student表的数据可以转换为一个Student的对象，这个对象称为pojo
     */
    private static class Student{
        private int stuId;
        private String loginPwd;
        private String studentName;
        private String gender;
        private int gradeID;
        private String phone;
        private String address;
        private Date born;

        public int getStuId() {
            return stuId;
        }

        public void setStuId(int stuId) {
            this.stuId = stuId;
        }

        public String getLoginPwd() {
            return loginPwd;
        }

        public void setLoginPwd(String loginPwd) {
            this.loginPwd = loginPwd;
        }

        public String getStudentName() {
            return studentName;
        }

        public void setStudentName(String studentName) {
            this.studentName = studentName;
        }

        public String getGender() {
            return gender;
        }

        public void setGender(String gender) {
            this.gender = gender;
        }

        public int getGradeID() {
            return gradeID;
        }

        public void setGradeID(int gradeID) {
            this.gradeID = gradeID;
        }

        public String getPhone() {
            return phone;
        }

        public void setPhone(String phone) {
            this.phone = phone;
        }

        public String getAddress() {
            return address;
        }

        public void setAddress(String address) {
            this.address = address;
        }

        public Date getBorn() {
            return born;
        }

        public void setBorn(Date born) {
            this.born = born;
        }

        @Override
        public String toString() {
            SimpleDateFormat sdf=new SimpleDateFormat("YYYY-MM-DD");
            String format = sdf.format(born);
            return "Student{" +
                    "stuId=" + stuId +
                    ", loginPwd='" + loginPwd + '\'' +
                    ", studentName='" + studentName + '\'' +
                    ", gender='" + gender + '\'' +
                    ", gradeID=" + gradeID +
                    ", phone='" + phone + '\'' +
                    ", address='" + address + '\'' +
                    ", born=" + format +
                    '}';
        }
    }
}
